pacman::p_load(readr,dplyr,lubridate,readxl,stringr)
rm(list=ls())
################################################################################


######################## Brazil

#Exchange rate
df <- read_csv("../../data/prices/raw/CCUSMA02BRM618N.csv",show_col_types = FALSE)
df <- df %>% mutate(timestamp = ymd(date),year = year(timestamp), month = month(timestamp)) %>%
  dplyr::select(year, real_usd_rate) %>% group_by(year) %>%
  summarize(real_usd_rate = mean(real_usd_rate, na.rm = TRUE)) %>% ungroup()
write_csv(df, "../../data/prices/clean/fred_real_usd_rate.csv.gz")

#CPI
cpi <- read_csv("../../data/prices/raw/BRACPIALLMINMEI.csv",show_col_types = FALSE)
cpi <- cpi %>% mutate(timestamp = ymd(date),year = year(timestamp),month = month(timestamp)) %>%
  dplyr::select(year, cpi) %>% group_by(year) %>% summarize(cpi = mean(cpi, na.rm = TRUE)) %>% ungroup()
base_cpi <- cpi %>% filter(year == 2010) %>% pull(cpi)
cpi <- cpi %>% mutate(base_cpi = base_cpi, factor = base_cpi / cpi) %>% dplyr::select(year, cpi, factor)
write_csv(cpi, "../../data/prices/clean/cpi_brazil.csv.gz")

####Census land values for livestock establishments

#1995
df_v <- read_excel("../../data/prices/raw/tabela499.xlsx", sheet = 1, skip = 4, .name_repair = "unique_quiet") |>  
  slice(1:(n() - 1)) |>  
  dplyr::select(1, 4) |>  
  rename(county_id = 1,value_r = 2) |>
  mutate(county_id = str_c("BR", as.character(county_id)),value_r = parse_number(as.character(value_r), na=c("-")))
df_a <- read_excel("../../data/prices/raw/tabela502.xlsx", sheet = 1, skip = 4, .name_repair = "unique_quiet") |>
  slice(1:(n() - 1)) |>
  dplyr::select(1, 4) |>
  rename(county_id = 1,area_ha = 2) |>
  mutate(county_id = str_c("BR", as.character(county_id)),area_ha = parse_number(as.character(area_ha), na=c("-")))
df95 <- df_v |> inner_join(df_a, by = "county_id") |> mutate(price_per_ha_current_R = value_r / area_ha,year = 1995L)

#2006
df_v <- read_excel("../../data/prices/raw/tabela822.xlsx", sheet = 1, skip = 6, .name_repair = "unique_quiet") |> 
  slice(1:(n() - 1)) |>  dplyr::select(1, 4) |>  rename(county_id = 1,value_r = 2) |>
  mutate(county_id = str_c("BR", as.character(county_id)),value_r = parse_number(as.character(value_r), na=c("-")) * 1000  )
df_a <- read_excel("../../data/prices/raw/tabela822.xlsx", sheet = 2, skip = 6, .name_repair = "unique_quiet") |>
  slice(1:(n() - 1)) |> dplyr::select(1, 4) |> rename(county_id = 1,area_ha = 2) |>
  mutate(county_id = str_c("BR", as.character(county_id)),area_ha = parse_number(as.character(area_ha), na=c("-")))
df06 <- df_v |> inner_join(df_a, by = "county_id") |> mutate(price_per_ha_current_R = value_r / area_ha,year = 2006L)

#2017
df_v <- read_excel("../../data/prices/raw/tabela6897.xlsx", sheet = 1, skip = 5, .name_repair = "unique_quiet") |>  
  slice(1:(n() - 1)) |>  dplyr::select(1, 5) |>  rename(county_id = 1,value_r = 2) |>
  mutate(county_id = str_c("BR", as.character(county_id)),value_r = parse_number(as.character(value_r), na=c("-","X")) * 1000 )
df_a <- read_excel("../../data/prices/raw/tabela6754.xlsx", sheet = 1, skip = 5, .name_repair = "unique_quiet") |>
  slice(1:(n() - 1)) |>dplyr::select(1, 5) |>  rename(county_id = 1,area_ha = 2) |>
  mutate(county_id = str_c("BR", as.character(county_id)),area_ha = parse_number(as.character(area_ha),na=c("-","X")))
df17 <- df_v |> inner_join(df_a, by = "county_id") |> mutate(price_per_ha_current_R = value_r / area_ha,year = 2017L)

#Append
df <- bind_rows(df95, df06, df17) |>dplyr::select(year, county_id, price_per_ha_current_R)
cpi <- read_csv("../../data/prices/clean/cpi_brazil.csv.gz",show_col_types = FALSE)
df <- df |>left_join(cpi, by = "year") |>mutate(price_per_ha_constant_R = price_per_ha_current_R * factor)

#Convert 
exchange <- read_csv("../../data/prices/clean/fred_real_usd_rate.csv.gz",show_col_types = FALSE)
df <- df |>left_join(exchange, by = "year") |>mutate(price_per_ha_current_USD = price_per_ha_current_R / real_usd_rate )
df <- df |>dplyr::select(-c(cpi, factor, real_usd_rate)) |>mutate(across(everything(), ~ ifelse(is.infinite(.), NA, .)))
write_csv(df, "../../data/prices/clean/pastureland_values_brazil.csv.gz")


######################## Argentina

df_nation <- read_csv("../../data/prices/raw/precios_tierra_ma.csv",show_col_types = FALSE)
write_csv(df_nation, "../../data/prices/clean/land_prices_nation_argentina.csv.gz")

